import pymysql
import json

host = '127.0.0.1'
user = 'root'
password = ''
port = 3306
dbname = 'yiqing'
with open("./database.json", "r") as f:  # 打开文件
    data = f.read()  # 读取文件
    data = json.loads(data)
    host = data.get('host')
    user = data.get('user')
    password = data.get('password')
    port = data.get('port')
    dbname = data.get('dbname')

def insertDb(data,table): #插入数据库
    db = pymysql.connect(host=host,user=user,password=password,port=port,db=dbname)
    cursor = db.cursor()
    keys = ', '.join(data.keys())
    values = ''
    for i in data:
        val = str(data[i]).replace("'", '"')
        values += "'"+val+"',"
    values = values.strip(',')
    sql = 'INSERT INTO {table}({keys}) VALUES ({values})'.format(table=table, keys=keys, values=values)
    try:
        cursor.execute(sql)
        # print('Successful')
        db.commit()
    except:
        # print('Failed')
        db.rollback()
        cursor.close()
    db.close()
    last_id = cursor.lastrowid
    return last_id

def updateDb(data,table,id):#更改数据库
    obj = ''
    for i in data:
        obj += i+"='"+(str)(data[i])+"',"
    obj = obj.strip(',')
    db = pymysql.connect(host=host,user=user,password=password,port=port,db=dbname)
    cursor = db.cursor()
    sql = "UPDATE {table} SET {obj} WHERE id={id}".format(table=table,obj=obj,id=id)
    try:
        cursor.execute(sql)
        # print('Successful')
        db.commit()
    except:
        # print('Failed')
        db.rollback()
        cursor.close()
    db.close()
def getOne(table,key,value):
    db = pymysql.connect(host=host,user=user,password=password,port=port,db=dbname)
    cursor = db.cursor()
    sql = "select * from {table} where {key}='{value}'".format(table=table,key=key,value=value)
    res = cursor.execute(sql)
    if(res == 0):
        return 0
    selectResultList = cursor.fetchall()
    return selectResultList[0][0]
